package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import conexao.ConnectionManager;

import modelo.Cd;
import modelo.Musica;
import modelo.Musico;

public class MusicaDAOImp implements MusicaDAO{

	private final String INSERT = "insert into musica (nome, tempoFaixa,idCd) values (?,?,?)";
	private final String UPDATE = "update musica set nome =?, tempoFaixa =?,idCd =? where idMusica = ?";
	private final String SELECT = "select * from musica where idMusica = ?";
	private final String DELETE = "delete from musica where idMusica = ?";
	private final String ALL = "select idMusica,nome, tempoFaixa,idCd from musica";
	private final String LIKE = "select idMusica,nome, tempoFaixa,idCd from musica where nome like ?";
	
	
	private ConnectionManager connectionManager;
	private static Connection connection;
	
	MusicaDAOImp() {
		this.openConnection();
	}

	@Override
	public Musica salvar(Musica e) {
		try {
			PreparedStatement ps = null;
			//1 crie um instancia de PreparedStatement
			//para obter a chave gerada pelo banco
			ps = connection.prepareStatement(INSERT);
			
			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getTempoFaixa());
			ps.setObject(3,e.getCd().getIdCd());
			
			
			ps.executeUpdate();
			
		} catch (SQLException ex) {
			Logger.getLogger(MusicaDAOImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return e;
	}
	

	@Override
	public void alterar(Musica e) {
		PreparedStatement ps = null;
		try {
			//2 crie uma instancia de PreparedStatement 
			//passe para ela a instrucao para realizar alteracoes em uma registro do banco 
			ps = connection.prepareStatement(UPDATE);
						
			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getTempoFaixa());
			ps.setObject(3,e.getCd().getIdCd());
			ps.setObject(4,e.getIdMusica());
			
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(MusicaDAOImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		
	}
		
	
	@Override
	public void deletar(Musica e) {
		try{
			PreparedStatement ps = null;
			ps = connection.prepareStatement(DELETE);
			//3 passe para o PreparedStatement o valor do id que deve ser excluido do banco de dados 
			ps.setObject(1, e.getIdMusica());
		
			ps.executeUpdate();
		
		} catch (SQLException ex) {
			Logger.getLogger(MusicaDAOImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
	
	}

	@Override
	public List<Musica> findAll() {
		List<Musica> musicas = new ArrayList<Musica>();
	
	try {
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = connection.prepareStatement(ALL);
		
		//4 Obtenha uma instancia de resultset
		rs = ps.executeQuery();
		
		while (rs.next()) {
			
			CdDAO cdDao = DAOFactory.getCdDAO();
			List<Cd> cds = cdDao.buscaCdPorId(rs.getInt("idCd"));
			
			Musica musica = new Musica();
			musica.setIdMusica(rs.getInt("idMusica"));
			musica.setNome(rs.getString("nome"));
			musica.setTempoFaixa(rs.getDouble("tempoFaixa"));
			musica.setCd(cds.get(0));
			
			musicas.add(musica);
			//5 crie uma instancia de cidade com os dados
			//obtidos do banco e adicione a lista de cidades
			
		}


	} catch (SQLException ex) {
		Logger.getLogger(MusicaDAOImp.class.getName()).log(Level.SEVERE, null, ex);
	} finally {
		this.closeConnection();
	}
	return musicas;
}
		
			

	@Override
	public boolean isConnectionClose() {
		try {
			return connection.isClosed();
		} catch (SQLException ex) {
			return false;
		}
	}

	@Override
	public void openConnection() {
		if ( connectionManager == null ) {
			connectionManager = ConnectionManager.getInstance();
		}
		
		if ( connection == null) {
			connection = connectionManager.getConnection();
		}
		
		if ( isConnectionClose() ) {
			connection = connectionManager.getConnection();
		}
		
	}

	@Override
	public void closeConnection() {
		if ( connection != null && connectionManager != null) {
			if ( ! this.isConnectionClose() ) {
				connectionManager.closeConnection(connection);
			}
		}		
		
	}

	@Override
	public List<Musica> buscaMusicaPorNome(Musica musica) {
		List<Musica> musicas = new ArrayList<Musica>();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = connection.prepareStatement(LIKE);
			//6 informe o parmetro para instrucao like
			ps.setObject(1, musica.getNome());
		
			rs = ps.executeQuery();

			while (rs.next()) {
				CdDAO cdDao = DAOFactory.getCdDAO();
				List<Cd> cds = cdDao.buscaCdPorId(rs.getInt("idCd"));
				musicas.add(new Musica(
						rs.getInt("idMusica"),
						rs.getString("nome"),
						rs.getDouble("tempoFaixa"),
						cds.get(0)));
			}

		} catch (SQLException ex) {
			Logger.getLogger(MusicaDAOImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return musicas;
	}
	

	@Override
	public List<Musica> buscaMusicaPorId(int idMusica) {
		List<Musica> musicas = new ArrayList<Musica>();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = connection.prepareStatement(SELECT);
			//6 informe o parmetro para instrucao like
			ps.setObject(1, idMusica);
		
			rs = ps.executeQuery();

			while (rs.next()) {
				CdDAO cdDao = DAOFactory.getCdDAO();
				List<Cd> cds = cdDao.buscaCdPorId(rs.getInt("idCd"));
				musicas.add(new Musica(
						rs.getInt("idMusica"),
						rs.getString("nome"),
						rs.getDouble("tempoFaixa"),
						cds.get(0)));
			}

		} catch (SQLException ex) {
			Logger.getLogger(MusicaDAOImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return musicas;
	}

}
